#ifndef _CM_SCRIPTS_H
#define _CM_SCRIPTS_H

#include <string>
using namespace std;

const wstring DEFAULT_DATABASE_NAME = L"dbVenusNetCollector.db";
const wstring PATH_DB = L"test.db";

const wstring DOMAIN_SCRIPT = L"CREATE TABLE domain ( "
  L"domain_id  integer PRIMARY KEY AUTOINCREMENT NOT NULL, "
  L"dc text, "
  L"fdnq text );";

const wstring COMPUTER_SCRIPT = L"CREATE TABLE computer ( "
  L"computer_id  integer PRIMARY KEY AUTOINCREMENT NOT NULL, "
  L"domain_id    integer NOT NULL, "
  L"os           varchar(255), "
  L"cn           varchar(255), "
  L"FOREIGN KEY (domain_id) "
  L"  REFERENCES domains(domain_id) );";

const wstring COLLECTION_SCRIPT = L"CREATE TABLE collectionTime ( "
  L"collectiontime_id  integer PRIMARY KEY AUTOINCREMENT NOT NULL, "
  L"computer_id  integer NOT NULL, "
  L"'date'       datetime, "
  L"FOREIGN KEY (computer_id) "
  L"  REFERENCES computer(computer_id) );";

const wstring PROCESS_SCRIPT = L"CREATE TABLE process ( "
  L"process_id                      integer PRIMARY KEY AUTOINCREMENT NOT NULL, "
  L"collectiontime_id               integer, "
  L"caption_att                     text, "
  L"commandLine_att                 text, "
  L"creationClassName_att           text, "
  L"creationDate_att                datetime, "
  L"csCreationClassName_att         text, "
  L"csName_att                      text, "
  L"description_att                 text, "
  L"executablePath_att              text, "
  L"executionState_att              integer, "
  L"handle_att                      text, "
  L"handleCount_att                 integer, "
  L"installDate_att                 datetime, "
  L"kernelModeTime_att              integer, "
  L"maximumWorkingSetSize_att       integer, "
  L"minimumWorkingSetSize_att       integer, "
  L"name_att                        text, "
  L"oSCreationClassName_att         text, "
  L"oSName_att                      text, "
  L"otherOperationCount_att         integer, "
  L"otherTransferCount_att          integer, "
  L"pageFaults_att                  integer, "
  L"pageFileUsage_att               integer, "
  L"parentProcessId_att             integer, "
  L"peakPageFileUsage_att           integer, "
  L"peakVirtualSize_att             integer, "
  L"peakWorkingSetSize_att          integer, "
  L"priority_att                    integer, "
  L"privatePageCount_att            integer, "
  L"processId_att                   integer, "
  L"quotaNonPagedPoolUsage_att      integer, "
  L"quotaPagedPoolUsage_att         integer, "
  L"quotaPeakNonPagedPoolUsage_att  integer, "
  L"quotaPeakPagedPoolUsage_att     integer, "
  L"readOperationCount_att          integer, "
  L"readTransferCount_att           integer, "
  L"sessionId_att                   integer, "
  L"status_att                      text, "
  L"terminationDate_att             datetime, "
  L"threadCount_att                 integer, "
  L"userModeTime_att                integer, "
  L"virtualSize_att                 integer, "
  L"windowsVersion_att              text, "
  L"workingSetSize_att              integer, "
  L"writeOperationCount_att         integer, "
  L"writeTransferCount_att          integer, "
  
  L"FOREIGN KEY (collectiontime_id) "
  L"  REFERENCES collectionTime(collectiontime_id));";

  const wstring SHARE_SCRIPT = L"CREATE TABLE share ( "
  L"share_id            integer PRIMARY KEY AUTOINCREMENT NOT NULL, "
  L"collectiontime_id   integer, "
  L"accessMask_att      integer, "
  L"allowMaximum_att    boolean, "
  L"caption_att         text, "
  L"description_att     text, "
  L"installDate_att     integer, "
  L"maximumAllowed_att  integer, "
  L"name_att            text, "
  L"path_att            text, "
  L"status_att          text, "
  L"type_att            text, "
  L"FOREIGN KEY (collectiontime_id) "
  L"  REFERENCES collectionTime(collectiontime_id) );";

  const wstring REGISTRY_SCRIPT = L"CREATE TABLE registry ( "
  L"registry_id        integer PRIMARY KEY AUTOINCREMENT NOT NULL, "
  L"parent_id          integer, "
  L"collectiontime_id  integer, "
  L"name_att           text, "
  L"path_att           text, "
  L"FOREIGN KEY (collectiontime_id) "
  L"  REFERENCES collectionTime(collectiontime_id) "
  L"FOREIGN KEY (parent_id) "
  L"  REFERENCES registry(registry_id) ); ";

  const wstring VALUE_SCRIPT = L"CREATE TABLE value ( "
  L"values_id    integer PRIMARY KEY AUTOINCREMENT NOT NULL, "
  L"registry_id  integer, "
  L"name_att     text, "
  L"type_att     text, "  
  L"FOREIGN KEY (registry_id)  "
  L"  REFERENCES registry(registry_id) ); ";

  const wstring DIRECTORY_SCRIPT = L"CREATE TABLE directory ( "
  L"directory_id       integer PRIMARY KEY NOT NULL, "
  L"parent_id          integer, "
  L"collectiontime_id  integer, "
  L"caption_att        text, "
  L"creationDate_att   datetime, "
  L"description_att    text, "
  L"installDate_att    datetime, "
  L"lastAccessed_att   datetime, "
  L"lastModified_att   datetime, "
  L"name_att           text, "
  L"status_att         text, "
  L"writeable_att      boolean, "
  L"FOREIGN KEY (parent_id) REFERENCES directory(directory_id) , "
  L"FOREIGN KEY (collectiontime_id) REFERENCES collectionTime(collectiontime_id) "
  L");";

  const wstring FILE_SCRIPT = L"CREATE TABLE file ( "
  L"file_id           integer PRIMARY KEY AUTOINCREMENT NOT NULL, "
  L"parent_id      integer, "
  L"collectiontime_id  integer, "
  L"caption_att       text, "
  L"creationDate_att  datetime, "
  L"description_att   text, "
  L"installDate_att   datetime, "
  L"lastAccessed_att  datetime, "
  L"lastModified_att  datetime, "
  L"name_att          text, "
  L"status_att        text, "
  L"writeable_att     boolean, "
  L"FOREIGN KEY (parent_id) REFERENCES directory(directory_id) , "
  L"FOREIGN KEY (collectiontime_id) REFERENCES collectionTime(collectiontime_id));";

  // scripts to insert
  const wstring INSERT_COMPUTER_SCRIPT = L"INSERT INTO computer "
    L"(computer_id, domain_id, cn, os) "
    L"VALUES( NULL, ?, ?, ?);" ;

  const wstring INSERT_SHARE_SCRIPT = L"INSERT INTO share "
    L"(share_id, collectiontime_id, accessMask_att, allowMaximum_att, caption_att, description_att, installDate_att, maximumAllowed_att, name_att, path_att, status_att, type_att) "
    L"VALUES( NULL, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);" ;

  const wstring INSERT_REGISTRY_SCRIPT =  L"INSERT INTO registry "
    L"(registry_id, parent_id, collectiontime_id, name_att, path_att) "
    L"VALUES( NULL, ?, ?, ?, ?);" ;

  const wstring INSERT_VALUE_SCRIPT =  L"INSERT INTO value "
    L"(values_id, registry_id, name_att, type_att) "
    L"VALUES( NULL, ?, ?, ?);" ;

  const wstring INSERT_PROCESS_SCRIPT = L"INSERT INTO process (process_id, collectiontime_id, caption_att,"
  L"commandLine_att, creationClassName_att, creationDate_att, csCreationClassName_att, csName_att,"
  L"description_att, executablePath_att, executionState_att, handle_att, handleCount_att,"
  L"installDate_att, kernelModeTime_att, maximumWorkingSetSize_att, minimumWorkingSetSize_att,"
  L"name_att, oSCreationClassName_att, oSName_att, otherOperationCount_att, otherTransferCount_att,"
  L"pageFaults_att, pageFileUsage_att, parentProcessId_att, peakPageFileUsage_att, peakVirtualSize_att,"
  L"peakWorkingSetSize_att, priority_att, privatePageCount_att, processId_att, quotaNonPagedPoolUsage_att,"
  L"quotaPagedPoolUsage_att, quotaPeakNonPagedPoolUsage_att, quotaPeakPagedPoolUsage_att,"
  L"readOperationCount_att, readTransferCount_att, sessionId_att, status_att, terminationDate_att,"
  L"threadCount_att, userModeTime_att, virtualSize_att, windowsVersion_att, workingSetSize_att,"
  L"writeOperationCount_att, writeTransferCount_att )"
  L"VALUES"
  L"(NULL, ?, ?, ?, ?, datetime(?, 'unixepoch', 'localtime'), ?, ?, ?, ?, ?, ?, ?, datetime(?, 'unixepoch', 'localtime'), "
  L" ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,"
  L"?, ?, ?, ?, ?, ?, ?, ?, ?, datetime(?, 'unixepoch', 'localtime'), ?, ?, ?, ?, ?, ?, ? );";

  const wstring INSERT_FILE_SCRIPT = L"INSERT INTO file (file_id, parent_id, collectiontime_id, caption_att,"
  L"creationDate_att, description_att, installDate_att,lastAccessed_att,lastModified_att, name_att, status_att, "
  L"writeable_att) "
  L"VALUES(NULL, ?, ?, ?, datetime(?, 'unixepoch', 'localtime'), ?, datetime(?, 'unixepoch', 'localtime'), "
  L" datetime(?, 'unixepoch', 'localtime'), datetime(?, 'unixepoch', 'localtime'), ?, ?, ?); ";

  const wstring INSERT_DIRECTORY_SCRIPT = L"INSERT INTO directory(directory_id, parent_id, "
  L"collectiontime_id, caption_att, creationDate_att, description_att, installDate_att, lastAccessed_att, "
  L"lastModified_att, name_att, status_att, writeable_att) "
  L"VALUES (NULL, ?, ?, ?, datetime(?, 'unixepoch', 'localtime'), ?, datetime(?, 'unixepoch', 'localtime'), "
  L" datetime(?, 'unixepoch', 'localtime'), datetime(?, 'unixepoch', 'localtime'), ?, ?, ?) ";

  const wstring SELECT_SHARE_SCRIPT = L"SELECT * FROM share WHERE collectiontime_id=?;";

  const wstring SELECT_REGISTRYPARENT_SCRIPT = L"SELECT * FROM registry WHERE parent_id=? AND collectiontime_id=?;";

  const wstring SELECT_REGISTRY_SCRIPT = L"SELECT * FROM registry WHERE parent_id=?;";
  
  const wstring SELECT_VALUE_SCRIPT = L"SELECT * FROM value WHERE registry_id=?;";

  const wstring SELECT_PROCESS_SCRIPT = L"SELECT * FROM process WHERE collectiontime_id = ? ;";

  const wstring SELECT_FILE_SCRIPT = L"SELECT * FROM file WHERE collectiontime_id =?;";

  const wstring SELECT_DIRECTORY_SCRIPT = L"SELECT * FROM directory WHERE collectiontime_id =?;";

#endif